﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Agresso.Interface.Layer.Gateway;
using System.Collections;
using System.Data.SqlClient;
using Agresso.Interface.Layer.SQL;
using Agresso.Interface.Layer.Domain;
using System.Data;

namespace Agresso.Interface.Layer.Service
{
    public class StatementService
    {
        DataBaseConnectivity m_dataBaseConectivity;
        SupplierCustomerService m_suppCusService;
        ProfitCentreService m_profitCentreService;
        ChargeTypeAccountMappingService m_chargeTypeAccountMappingService;
        VesselService m_vesselService;
        AlertService m_alertService;
        AcrBatcInputService m_AcrBatcInputService;
        ConfigurationSettingService m_configSettingService;
        ud_voyageimp_Service m_ud_voyageimp_Service;
        agldimvalueService m_agldimvalueService;
        ResponsibleUserService m_ResponsibleUserService;

        public StatementService()
        {
            m_dataBaseConectivity = new DataBaseConnectivity();
            m_suppCusService = new SupplierCustomerService();
            m_profitCentreService = new ProfitCentreService();
            m_vesselService = new VesselService();
            m_chargeTypeAccountMappingService = new ChargeTypeAccountMappingService();
            m_alertService = new AlertService();
            m_configSettingService = new ConfigurationSettingService();
            m_AcrBatcInputService = new AcrBatcInputService();
            m_ud_voyageimp_Service = new ud_voyageimp_Service();
            m_agldimvalueService = new agldimvalueService();
            m_ResponsibleUserService = new ResponsibleUserService();
        }
        public void ImportAllDataSoftmarToMiddleLayer(int invoiced)
        {
            try
            {
                IList statements = new ArrayList();
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.SoftmarSqlConnection;
                cmd.CommandText = StatementSQL.GetAllSoftmerStatementIgnoreVoyage_REV01();
                cmd.Parameters.Add("@invoiced", SqlDbType.Int).Value = invoiced;

                cmd.CommandTimeout = 300;
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    Statement po = new Statement(rd, false);
                    statements.Add(po);
                }
                rd.Close();

                IList invoiceList = new ArrayList();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = StatementSQL.GetStatementInvoiceMiddleLayer();
                cmd.CommandTimeout = 300;
                cmd.Parameters.Clear();
                rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    invoiceList.Add(rd[0]);
                }
                rd.Close();

                foreach (Statement statement in statements)
                {
                    cmd.Parameters.Clear();
                    if (!invoiceList.Contains(statement.statement_number))
                    {
                        cmd.Parameters.Add("@statement_number", SqlDbType.NVarChar).Value = statement.statement_number;
                        cmd.Parameters.Add("@charge_description", SqlDbType.VarChar).Value = statement.charge_description;
                        cmd.Parameters.Add("@charge_type", SqlDbType.NVarChar).Value = statement.charge_type;
                        cmd.Parameters.Add("@charge_amount", SqlDbType.Decimal).Value = statement.charge_amount;
                        cmd.Parameters.Add("@charge_address_amount", SqlDbType.Decimal).Value = statement.charge_address_amount;
                        cmd.Parameters.Add("@charge_co_comm_amount", SqlDbType.Decimal).Value = statement.charge_co_comm_amount;

                        cmd.Parameters.Add("@headfixture_type", SqlDbType.NVarChar).Value = statement.headfixture_type;
                        cmd.Parameters.Add("@head_fixture_name", SqlDbType.NVarChar).Value = statement.head_fixture_name;
                        cmd.Parameters.Add("@profit_centre", SqlDbType.NVarChar).Value = statement.profit_centre;
                        cmd.Parameters.Add("@statement_header", SqlDbType.NVarChar).Value = statement.statement_header;
                        cmd.Parameters.Add("@registered_owner", SqlDbType.NVarChar).Value = statement.registered_owner;
                        cmd.Parameters.Add("@vessel_name", SqlDbType.NVarChar).Value = statement.vessel_name;
                        cmd.Parameters.Add("@voyage_no", SqlDbType.Int).Value = statement.voyage_no;
                        cmd.Parameters.Add("@disponent_owner", SqlDbType.NVarChar).Value = statement.disponent_owner;
                        cmd.Parameters.Add("@created_by", SqlDbType.NVarChar).Value = statement.created_by;
                        cmd.Parameters.Add("@statement_date", SqlDbType.DateTime).Value = statement.statement_date;
                        cmd.Parameters.Add("@statement_due_date", SqlDbType.DateTime).Value = statement.statement_due_date;
                        cmd.Parameters.Add("@statement_issued", SqlDbType.NVarChar).Value = statement.statement_issued;
                        cmd.Parameters.Add("@statement_currency", SqlDbType.NVarChar).Value = statement.statement_currency;
                        cmd.Parameters.Add("@remittance_amount", SqlDbType.Decimal).Value = statement.remittance_amount;

                        cmd.Parameters.Add("@start_voyage_no", SqlDbType.Int).Value = statement.start_voyage_no;
                        cmd.Parameters.Add("@statement_type", SqlDbType.NVarChar).Value = statement.statement_type;

                        cmd.Parameters.Add("@company", SqlDbType.NVarChar).Value = statement.company;
                        cmd.Parameters.Add("@raised_to_final_by", SqlDbType.NVarChar).Value = statement.raised_to_final_by;
                        cmd.Parameters.Add("@voyage_ref", SqlDbType.NVarChar).Value = statement.voyage_ref;
                        cmd.Parameters.Add("@statement_total", SqlDbType.Decimal).Value = statement.statement_total;
                        cmd.Parameters.Add("@narrative_line", SqlDbType.Int).Value = statement.narrative_line;
                        cmd.Parameters.Add("@charge_id", SqlDbType.Int).Value = statement.charge_id;
                        cmd.Parameters.Add("@fixed_by", SqlDbType.NVarChar).Value = statement.fixed_by;
                        cmd.Parameters.Add("@vessel_code", SqlDbType.NVarChar).Value = statement.vessel_sid;

                        cmd.Parameters.Add("@pickup_flag", SqlDbType.NVarChar).Value = "0";
                        cmd.Parameters.Add("@insert_date", SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.CommandText = StatementSQL.InsertStatementTemp();
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public void ImportAllDataSoftmarToMiddleLayer(int invoiced, string InvoiceNo)
        {
            try
            {
                IList statements = new ArrayList();
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.SoftmarSqlConnection;
                cmd.CommandText = StatementSQL.GetAllSoftmerStatementIgnoreVoyage_REV01_byInvoice();
                cmd.Parameters.Add("@invoiced", SqlDbType.Int).Value = invoiced;
                cmd.Parameters.Add("@InvoiceNo", SqlDbType.NVarChar).Value = InvoiceNo;

                cmd.CommandTimeout = 300;
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    Statement po = new Statement(rd, false);
                    statements.Add(po);
                }
                rd.Close();

                IList invoiceList = new ArrayList();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = StatementSQL.GetStatementInvoiceMiddleLayer();
                cmd.CommandTimeout = 300;
                cmd.Parameters.Clear();
                rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    invoiceList.Add(rd[0]);
                }
                rd.Close();

                foreach (Statement statement in statements)
                {
                    cmd.Parameters.Clear();
                    if (!invoiceList.Contains(statement.statement_number))
                    {
                        cmd.Parameters.Add("@statement_number", SqlDbType.NVarChar).Value = statement.statement_number;
                        cmd.Parameters.Add("@charge_description", SqlDbType.VarChar).Value = statement.charge_description;
                        cmd.Parameters.Add("@charge_type", SqlDbType.NVarChar).Value = statement.charge_type;
                        cmd.Parameters.Add("@charge_amount", SqlDbType.Decimal).Value = statement.charge_amount;
                        cmd.Parameters.Add("@charge_address_amount", SqlDbType.Decimal).Value = statement.charge_address_amount;
                        cmd.Parameters.Add("@charge_co_comm_amount", SqlDbType.Decimal).Value = statement.charge_co_comm_amount;

                        cmd.Parameters.Add("@headfixture_type", SqlDbType.NVarChar).Value = statement.headfixture_type;
                        cmd.Parameters.Add("@head_fixture_name", SqlDbType.NVarChar).Value = statement.head_fixture_name;
                        cmd.Parameters.Add("@profit_centre", SqlDbType.NVarChar).Value = statement.profit_centre;
                        cmd.Parameters.Add("@statement_header", SqlDbType.NVarChar).Value = statement.statement_header;
                        cmd.Parameters.Add("@registered_owner", SqlDbType.NVarChar).Value = statement.registered_owner;
                        cmd.Parameters.Add("@vessel_name", SqlDbType.NVarChar).Value = statement.vessel_name;
                        cmd.Parameters.Add("@voyage_no", SqlDbType.Int).Value = statement.voyage_no;
                        cmd.Parameters.Add("@disponent_owner", SqlDbType.NVarChar).Value = statement.disponent_owner;
                        cmd.Parameters.Add("@created_by", SqlDbType.NVarChar).Value = statement.created_by;
                        cmd.Parameters.Add("@statement_date", SqlDbType.DateTime).Value = statement.statement_date;
                        cmd.Parameters.Add("@statement_due_date", SqlDbType.DateTime).Value = statement.statement_due_date;
                        cmd.Parameters.Add("@statement_issued", SqlDbType.NVarChar).Value = statement.statement_issued;
                        cmd.Parameters.Add("@statement_currency", SqlDbType.NVarChar).Value = statement.statement_currency;
                        cmd.Parameters.Add("@remittance_amount", SqlDbType.Decimal).Value = statement.remittance_amount;

                        cmd.Parameters.Add("@start_voyage_no", SqlDbType.Int).Value = statement.start_voyage_no;
                        cmd.Parameters.Add("@statement_type", SqlDbType.NVarChar).Value = statement.statement_type;

                        cmd.Parameters.Add("@company", SqlDbType.NVarChar).Value = statement.company;
                        cmd.Parameters.Add("@raised_to_final_by", SqlDbType.NVarChar).Value = statement.raised_to_final_by;
                        cmd.Parameters.Add("@voyage_ref", SqlDbType.NVarChar).Value = statement.voyage_ref;
                        cmd.Parameters.Add("@statement_total", SqlDbType.Decimal).Value = statement.statement_total;
                        cmd.Parameters.Add("@narrative_line", SqlDbType.Int).Value = statement.narrative_line;
                        cmd.Parameters.Add("@charge_id", SqlDbType.Int).Value = statement.charge_id;
                        cmd.Parameters.Add("@fixed_by", SqlDbType.NVarChar).Value = statement.fixed_by;
                        cmd.Parameters.Add("@vessel_code", SqlDbType.NVarChar).Value = statement.vessel_sid;

                        cmd.Parameters.Add("@pickup_flag", SqlDbType.NVarChar).Value = "0";
                        cmd.Parameters.Add("@insert_date", SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.CommandText = StatementSQL.InsertStatementTemp();
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public int GetVoyageNumberByStatementNumber(string statement_number)
        {
            int result = 0;
            m_dataBaseConectivity.SoftmarSqlConnection.Open();
            try
            {
                SqlCommand cmd = new SqlCommand(StatementSQL.GetMaxVoyageNoByStatementNo(), m_dataBaseConectivity.SoftmarSqlConnection);
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@statement_number", SqlDbType.NVarChar).Value = statement_number;
                object p = cmd.ExecuteScalar();
                if (!(p is DBNull))
                    result = Convert.ToInt32(p);
                return result;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
            }
        }
        public int GetVoyageNumberByVesselName(string vesselname)
        {
            int result = 0;
            m_dataBaseConectivity.SoftmarSqlConnection.Open();
            try
            {
                SqlCommand cmd = new SqlCommand(StatementSQL.GetMaxVoyageNoByVesselName(), m_dataBaseConectivity.SoftmarSqlConnection);
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@vesselname", SqlDbType.NVarChar).Value = vesselname;
                object p = cmd.ExecuteScalar();
                if (!(p is DBNull))
                    result = Convert.ToInt32(p);
                return result;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
            }
        }
        //int test = 0;
        public void ExecuteStatementInterface()
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                IList statement_numbers = new ArrayList();
                SqlCommand cmd = new SqlCommand(StatementSQL.GetPendingAllStatementNumberInterface(), m_dataBaseConectivity.InterfaceSqlConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    statement_numbers.Add(rd["statement_number"].ToString());
                }
                rd.Close();
                IList statements = new ArrayList();
                IList statementsCommissionCredit = new ArrayList();

                foreach (string statement_number in statement_numbers)
                {
                    cmd.CommandText = StatementSQL.GetPendingStatementInterfaceByStatementNumber();
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@statement_number", SqlDbType.NVarChar).Value = statement_number;
                    rd = cmd.ExecuteReader();
                    Statement header = null;
                    while (rd.Read())
                    {
                        if (header == null)
                        {
                            header = new Statement(rd, true);
                            header.voyage_no = 999;
                        }
                        Statement item = new Statement(rd, true);
                        item.voyage_no = 999;
                        item.Header = header;
                        header.Items.Add(item);
                    }
                    rd.Close();
                    if (header.statement_type.Trim() == "Commission Credit")
                        statementsCommissionCredit.Add(header);
                    else
                        statements.Add(header);
                }

                string prevclient = "";
                int batch = Convert.ToInt16(DateTime.Now.ToString("ss"));

                foreach (Statement stt in statements)
                {
                    // stt.voyage_no = 999;
                    //   test++;
                    //   if (test == 223)
                    //   { 
                    //  }
                    //if (stt.voyage_no == 0)
                    //    stt.voyage_no = GetVoyageNumberByStatementNumber(stt.statement_number);
                    //if (stt.voyage_no == 0)
                    //    stt.voyage_no = GetVoyageNumberByVesselName(stt.vessel_name);
                    SupplierCustomer supplier = m_suppCusService.GetSupplierMapping(stt.disponent_owner == "" ? stt.registered_owner : stt.disponent_owner);
                    ProfitCentre profitCentre = m_profitCentreService.GetProfitCentreMapping(stt.profit_centre);
                    ResponsibleUser user = m_ResponsibleUserService.GetResponsibleUserByName(stt.fixed_by, "POSTFIX");
                    m_vesselService.FindVesselByName(stt.vessel_name,stt.vessel_sid, stt.profit_centre); // vessel validation inserting if not found------------
                    // ChargeTypeAccountMapping chargeType = m_chargeTypeAccountMappingService.GetChargeTypeAccountMapping(stt.charge_type);
                    string alert = "";
                    if (supplier.agr_supp_apar_id == "") { alert += string.Format("-[E005] Unable to find apar_ID for Company '{0}' \r\n", supplier.full_name); };
                    if (profitCentre.agr_dim_value == "") { alert += string.Format("-[E004] Unable to find Dim_Val for Profit Centre '{0}' \r\n", profitCentre.full_name); }//alert
                    if (profitCentre.agr_rel_value == "") { alert += string.Format("-[E008] Unable to find Cost Centre Code for Profit Centre '{0}' \r\n", profitCentre.full_name); }//alert
                    if (profitCentre.client == "") { alert += string.Format("-[E007] Unable to find client for Profit Centre '{0}' \r\n", profitCentre.full_name); }//alert
                    Vessel profitCentreVessel = m_vesselService.GetVesselMappingByProfitCentreIDAndVesselName(profitCentre.profit_centre_id, stt.vessel_name.Trim(), profitCentre.client);
                    //28112013 bool validVoyage = m_ud_voyageimp_Service.IsRegisteredVoyage(profitCentreVessel == null ? "" : profitCentreVessel.agr_dim_value, stt.voyage_no.ToString());
                    if (supplier.apar_gr_id.Trim().ToUpper() == "SBR")
                    {
                        agldimvalue icVessel = m_agldimvalueService.GetICVesselDimValue(profitCentreVessel == null ? "XXX" : profitCentreVessel.agr_dim_value);
                        if (profitCentreVessel != null && icVessel == null) { alert += string.Format("-[E012] Unable to find IC Vessel value Code for Vessel '{0}' \r\n", profitCentreVessel.agr_dim_value); } //alert could not  find vessel dim val
                    }
                    // agldimvalue icVoyage = m_agldimvalueService.GetICVoyageDimValue((profitCentreVessel == null ? "XXX" : (profitCentreVessel.agr_dim_value + "-V" + stt.voyage_no.ToString().PadLeft(3, '0'))));

                    if (profitCentreVessel == null) { alert += string.Format("-[E015] Unable to find Vessel '{0}' for Profit Centre '{1} {2}' \r\n", stt.vessel_name.Trim(), profitCentre.full_name, profitCentre.agr_dim_value); }
                    if (profitCentreVessel != null && profitCentreVessel.agr_dim_value == "") { alert += string.Format("-[E010] Unable to find Dim_Val Code for Vessel '{0}' \r\n", profitCentreVessel.vessel_name); }//alert
                    if (profitCentreVessel != null && profitCentreVessel.agr_rel_value == "") { alert += string.Format("-[E008] Unable to find Cost Centre Code for Vessel '{0}' \r\n", profitCentreVessel.vessel_name); }//alert
                    //  if (profitCentreVessel != null && icVoyage == null) { alert += string.Format("-Unable to find IC Voyage value Code for Voyage '{0}' \r\n", profitCentreVessel.agr_dim_value + "-V" + stt.voyage_no.ToString().PadLeft(3, '0')); } //alert could not  find vessel dim val

                    //28112013 if (!validVoyage)
                    //{
                    //    alert += string.Format("-[E016] Unable to Find Voyage in Voyage Master, Vessel: '{0}' Voyage: '{1}' Code:'{2}' \r\n", stt.vessel_name.Trim(), stt.voyage_no,
                    //        (profitCentreVessel != null ? profitCentreVessel.agr_dim_value : "") + "-V" + stt.voyage_no.ToString().PadLeft(3, '0'));
                    //}

                    else if (stt.headfixture_type == "")
                    {
                        alert += string.Format("-[E026] The statement contains no HeadFixture Type");
                    }

                    foreach (Statement item in stt.Items)
                    {
                        ChargeTypeAccountMapping itm = m_chargeTypeAccountMappingService.GetChargeTypeAccountMapping(item.charge_type);
                        if (stt.headfixture_type == "TC Charter")
                        {
                            if (itm.tcrelet_account == "") { alert += string.Format("-[E017] Unable to find Account Map for Charge Type '{0}' TC Charter", item.charge_type); } //alert coudnot map account on charge type :---------
                        }
                        else if (stt.headfixture_type == "BB")
                        {
                            if (itm.bareboatrelet_account == "") { alert += string.Format("-[E017] Unable to find Account Map for Charge Type '{0}'  Bareboat", item.charge_type); } //alert coudnot map account on charge type :---------
                        }
                    }

                    if (alert != "")
                    {
                        foreach (Statement item in stt.Items)
                        {
                            updateRemarks(item.i_statement_id, alert, item.remarks, "0");
                        }
                        //m_alertService.CheckAndUpdateAlert(stt.i_statement_id.ToString(), AlertType.SOFTMAR_STATEMENT, AlertStatus.PENDING,
                        //    string.Format("Failed while interfacing Invoice No.{0}, Invoice Date :{3}, Company Name : {1}, Description : {2}  \r\n", stt.statement_number, stt.registered_owner == "" ? stt.disponent_owner : stt.registered_owner
                        //    , stt.charge_description, stt.statement_date.ToString("dd-MMM-yyyy")) + alert, stt.statement_number);
                        continue;
                    }
                    //else
                    //{
                    //    m_alertService.CheckAndUpdateAlert(stt.i_statement_id.ToString(), AlertType.SOFTMAR_STATEMENT, AlertStatus.RESOLVED,
                    //          string.Format("Failed while interfacing Invoice No.{0}, Invoice Date :{3}, Company Name : {1}, Description : {2}  \r\n", stt.statement_number, stt.registered_owner == "" ? stt.disponent_owner : stt.registered_owner
                    //        , stt.charge_description, stt.statement_date.ToString("dd-MMM-yyyy")) + alert, stt.statement_number);
                    //}
                    IList acrbatchinputList = new ArrayList();
                    int voucherNumber = GetVoucherRunningNumber();
                    if (prevclient != profitCentre.client)
                        batch++;
                    string batchID = "SC" + DateTime.Now.ToString("ddMMyyyyHHmm") + batch.ToString().PadLeft(2, '0');
                    prevclient = profitCentre.client;
                    //string batchID = "SC" + DateTime.Now.ToString("ddMMyyyyHHmmss");

                    acrbatchinput acrbatchinput_AP = new acrbatchinput();
                    acrbatchinput_AP.voucher_type = "SC";
                    acrbatchinput_AP.trans_type = "AP";
                    acrbatchinput_AP.client = profitCentre.client;
                    acrbatchinput_AP.apar_type = "P";
                    acrbatchinput_AP.apar_id = supplier.agr_supp_apar_id;
                    acrbatchinput_AP.account = "";
                    acrbatchinput_AP.dim_1 = profitCentre.agr_rel_value;
                    acrbatchinput_AP.dim_2 = "";// profitCentreVessel.agr_rel_value;
                    acrbatchinput_AP.dim_3 = "";
                    acrbatchinput_AP.dim_4 = profitCentreVessel.agr_dim_value + "-V" + stt.voyage_no.ToString().PadLeft(3, '0');
                    acrbatchinput_AP.dim_5 = profitCentreVessel.agr_dim_value;
                    acrbatchinput_AP.dim_6 = profitCentre.agr_dim_value;
                    acrbatchinput_AP.dim_7 = "";
                    acrbatchinput_AP.tax_code = "0";
                    acrbatchinput_AP.tax_system = "";
                    acrbatchinput_AP.currency = stt.statement_currency;
                    acrbatchinput_AP.cur_amount = -stt.statement_total;//-ci.invoice_amount;
                    acrbatchinput_AP.amount = -stt.statement_total;//-ci.invoice_amount;
                    acrbatchinput_AP.description = stt.charge_description + "-" + stt.statement_number + "-" + stt.raised_to_final_by;
                    acrbatchinput_AP.trans_date = stt.statement_date;
                    acrbatchinput_AP.voucher_date = stt.statement_date;
                    acrbatchinput_AP.voucher_no = voucherNumber;// 1;
                    acrbatchinput_AP.ext_inv_ref = stt.statement_number;
                    acrbatchinput_AP.status = "N";
                    acrbatchinput_AP.due_date = stt.statement_due_date;
                    acrbatchinput_AP.period = Convert.ToInt32(DateTime.Today.ToString("yyyyMM"));
                    acrbatchinput_AP.responsible = user.agresso_user;//31122013 "DEV01";
                    acrbatchinput_AP.ext_ref = user.agresso_user;//31122013 "DEV01";
                    acrbatchinput_AP.batch_id = batchID;
                    acrbatchinput_AP.interface_ = "SC";
                    acrbatchinput_AP.sequence_no = GetSequenceRunningNumber();
                    acrbatchinput_AP.disc_date = acrbatchinput_AP.trans_date;
                    acrbatchinput_AP.arrival_date = acrbatchinput_AP.trans_date;
                    acrbatchinputList.Add(acrbatchinput_AP);

                    decimal totalGL = 0;
                    foreach (Statement item in stt.Items)
                    {
                        if (item.narrative_line == 90) //29-11-2013
                        {
                            continue;
                        }
                        if (item.narrative_line == 70)
                        {
                            ChargeTypeAccountMapping cad = m_chargeTypeAccountMappingService.GetChargeTypeAccountMapping("Charge Address Comm");
                            acrbatchinput acrbatchinput_GL_ca = new acrbatchinput();
                            acrbatchinput_GL_ca.voucher_type = "SC";
                            acrbatchinput_GL_ca.trans_type = "GL";
                            acrbatchinput_GL_ca.client = profitCentre.client;
                            acrbatchinput_GL_ca.apar_type = "";
                            acrbatchinput_GL_ca.apar_id = supplier.agr_supp_apar_id;
                            acrbatchinput_GL_ca.account = stt.headfixture_type.Trim() == "TC Charter" ? cad.tcrelet_account : cad.bareboatrelet_account;// stt.headfixture_type.Trim() == "TC Charter" ? chargeType.tcrelet_account : chargeType.vcrelet_account;
                            acrbatchinput_GL_ca.dim_1 = profitCentreVessel.agr_rel_value;//"";
                            acrbatchinput_GL_ca.dim_2 = "";// profitCentreVessel.agr_rel_value;
                            acrbatchinput_GL_ca.dim_3 = "";
                            acrbatchinput_GL_ca.dim_4 = profitCentreVessel.agr_dim_value + "-V" + stt.voyage_no.ToString().PadLeft(3, '0');
                            acrbatchinput_GL_ca.dim_5 = profitCentreVessel.agr_dim_value;
                            acrbatchinput_GL_ca.dim_6 = profitCentre.agr_dim_value;
                            acrbatchinput_GL_ca.dim_7 = "";
                            acrbatchinput_GL_ca.tax_code = stt.headfixture_type.Trim() == "TC Charter" ?
                                supplier.country_code_sup == "SG" ? cad.tcrelet_tax_account_singapore : cad.tcrelet_tax_account_ovs
                                : supplier.country_code_sup == "SG" ? cad.bareboatrelet_tax_account_singapore : cad.bareboatrelet_tax_account_ovs;//"0";
                            acrbatchinput_GL_ca.tax_system = "";
                            acrbatchinput_GL_ca.currency = stt.statement_currency;
                            acrbatchinput_GL_ca.cur_amount = item.charge_amount;// 04092013 -item.charge_address_amount;//ci.invoice_amount;
                            acrbatchinput_GL_ca.amount = item.charge_amount;// 04092013 -item.charge_address_amount;//ci.invoice_amount;
                            acrbatchinput_GL_ca.description = "Address Commission";//stt.charge_description + "-" + stt.statement_number + "-" + stt.raised_to_final_by;
                            acrbatchinput_GL_ca.trans_date = stt.statement_date;
                            acrbatchinput_GL_ca.voucher_date = stt.statement_date;
                            acrbatchinput_GL_ca.voucher_no = voucherNumber;//1;
                            acrbatchinput_GL_ca.ext_inv_ref = stt.statement_number;
                            acrbatchinput_GL_ca.status = "N";
                            acrbatchinput_GL_ca.due_date = stt.statement_due_date;
                            acrbatchinput_GL_ca.period = Convert.ToInt32(DateTime.Today.ToString("yyyyMM"));
                            acrbatchinput_GL_ca.responsible = user.agresso_user;//31122013 "DEV01";
                            acrbatchinput_GL_ca.ext_ref = user.agresso_user;//31122013 "DEV01";
                            acrbatchinput_GL_ca.batch_id = batchID;
                            acrbatchinput_GL_ca.interface_ = "SC";
                            acrbatchinput_GL_ca.disc_date = acrbatchinput_AP.trans_date;
                            acrbatchinput_GL_ca.arrival_date = acrbatchinput_AP.trans_date;
                            acrbatchinput_GL_ca.sequence_no = GetSequenceRunningNumber();
                            acrbatchinputList.Add(acrbatchinput_GL_ca);
                            totalGL += acrbatchinput_GL_ca.amount;
                        }
                        else if (item.narrative_line == 80)
                        {
                            ChargeTypeAccountMapping cam = m_chargeTypeAccountMappingService.GetChargeTypeAccountMapping("Charge Collect Comm");
                            acrbatchinput acrbatchinput_GL_cc = new acrbatchinput();
                            acrbatchinput_GL_cc.voucher_type = "SC";
                            acrbatchinput_GL_cc.trans_type = "GL";
                            acrbatchinput_GL_cc.client = profitCentre.client;
                            acrbatchinput_GL_cc.apar_type = "";
                            acrbatchinput_GL_cc.apar_id = supplier.agr_supp_apar_id;
                            acrbatchinput_GL_cc.account = stt.headfixture_type.Trim() == "TC Charter" ? cam.tcrelet_account : cam.bareboatrelet_account;//stt.headfixture_type.Trim() == "TC Charter" ? chargeType.tcrelet_account : chargeType.vcrelet_account;
                            acrbatchinput_GL_cc.dim_1 = profitCentreVessel.agr_rel_value;//"";
                            acrbatchinput_GL_cc.dim_2 = "";// profitCentreVessel.agr_rel_value;
                            acrbatchinput_GL_cc.dim_3 = "";
                            acrbatchinput_GL_cc.dim_4 = profitCentreVessel.agr_dim_value + "-V" + stt.voyage_no.ToString().PadLeft(3, '0');
                            acrbatchinput_GL_cc.dim_5 = profitCentreVessel.agr_dim_value;
                            acrbatchinput_GL_cc.dim_6 = profitCentre.agr_dim_value;
                            acrbatchinput_GL_cc.dim_7 = "";
                            //acrbatchinput_GL_cc.tax_code = stt.headfixture_type.Trim() == "TC Charter" ? cam.tcrelet_tax_account_singapore : cam.vcrelet_tax_account_singapore;//"0";
                            acrbatchinput_GL_cc.tax_code = stt.headfixture_type.Trim() == "TC Charter" ?
                                supplier.country_code_sup == "SG" ? cam.tcrelet_tax_account_singapore : cam.tcrelet_tax_account_ovs
                                : supplier.country_code_sup == "SG" ? cam.bareboatrelet_tax_account_singapore : cam.bareboatrelet_tax_account_ovs;//"0";
                            acrbatchinput_GL_cc.tax_system = "";
                            acrbatchinput_GL_cc.currency = stt.statement_currency;
                            acrbatchinput_GL_cc.cur_amount = item.charge_amount;// 04092013 -item.charge_co_comm_amount;//ci.invoice_amount;
                            acrbatchinput_GL_cc.amount = item.charge_amount;// 04092013  -item.charge_co_comm_amount;//ci.invoice_amount;
                            acrbatchinput_GL_cc.description = "Charge Collect Commission";//stt.charge_description + "-" + stt.statement_number + "-" + stt.raised_to_final_by;
                            acrbatchinput_GL_cc.trans_date = stt.statement_date;
                            acrbatchinput_GL_cc.voucher_date = stt.statement_date;
                            acrbatchinput_GL_cc.voucher_no = voucherNumber;//1;
                            acrbatchinput_GL_cc.ext_inv_ref = stt.statement_number;
                            acrbatchinput_GL_cc.status = "N";
                            acrbatchinput_GL_cc.due_date = stt.statement_due_date;
                            acrbatchinput_GL_cc.period = Convert.ToInt32(DateTime.Today.ToString("yyyyMM"));
                            acrbatchinput_GL_cc.responsible = user.agresso_user;//31122013 "DEV01";
                            acrbatchinput_GL_cc.ext_ref = user.agresso_user;//31122013 "DEV01";
                            acrbatchinput_GL_cc.batch_id = batchID;
                            acrbatchinput_GL_cc.interface_ = "SC";
                            acrbatchinput_GL_cc.disc_date = acrbatchinput_AP.trans_date;
                            acrbatchinput_GL_cc.arrival_date = acrbatchinput_AP.trans_date;
                            acrbatchinput_GL_cc.sequence_no = GetSequenceRunningNumber();
                            acrbatchinputList.Add(acrbatchinput_GL_cc);
                            totalGL += acrbatchinput_GL_cc.amount;
                        }
                        else
                        {
                            ChargeTypeAccountMapping cg = m_chargeTypeAccountMappingService.GetChargeTypeAccountMapping(item.charge_type);
                            acrbatchinput acrbatchinput_GL = new acrbatchinput();
                            acrbatchinput_GL.voucher_type = "SC";
                            acrbatchinput_GL.trans_type = "GL";
                            acrbatchinput_GL.client = profitCentre.client;
                            acrbatchinput_GL.apar_type = "";
                            acrbatchinput_GL.apar_id = supplier.agr_supp_apar_id;
                            acrbatchinput_GL.account = stt.headfixture_type.Trim() == "TC Charter" ? cg.tcrelet_account : cg.bareboatrelet_account; //stt.headfixture_type.Trim() == "TC Charter" ? cg.tcrelet_account : cg.vcrelet_account;
                            acrbatchinput_GL.dim_1 = profitCentreVessel.agr_rel_value;//"";
                            acrbatchinput_GL.dim_2 = "";// profitCentreVessel.agr_rel_value;
                            acrbatchinput_GL.dim_3 = "";
                            acrbatchinput_GL.dim_4 = profitCentreVessel.agr_dim_value + "-V" + stt.voyage_no.ToString().PadLeft(3, '0');
                            acrbatchinput_GL.dim_5 = profitCentreVessel.agr_dim_value;
                            acrbatchinput_GL.dim_6 = profitCentre.agr_dim_value;
                            acrbatchinput_GL.dim_7 = "";
                            acrbatchinput_GL.tax_code =
                                stt.headfixture_type.Trim() == "TC Charter" ?
                                supplier.country_code_sup == "SG" ? cg.tcrelet_tax_account_singapore : cg.tcrelet_tax_account_ovs
                                : supplier.country_code_sup == "SG" ? cg.bareboatrelet_tax_account_singapore : cg.bareboatrelet_tax_account_ovs;//"0";
                            acrbatchinput_GL.tax_system = "";
                            acrbatchinput_GL.currency = stt.statement_currency;
                            acrbatchinput_GL.cur_amount = item.charge_amount;//ci.invoice_amount;
                            acrbatchinput_GL.amount = item.charge_amount;//ci.invoice_amount;
                            acrbatchinput_GL.description = item.charge_type + " : " + item.charge_description + "-" + item.statement_number + "-" + item.raised_to_final_by;
                            acrbatchinput_GL.trans_date = stt.statement_date;
                            acrbatchinput_GL.voucher_date = stt.statement_date;
                            acrbatchinput_GL.voucher_no = voucherNumber;//1;
                            acrbatchinput_GL.ext_inv_ref = stt.statement_number;
                            acrbatchinput_GL.status = "N";
                            acrbatchinput_GL.due_date = stt.statement_due_date;
                            acrbatchinput_GL.period = Convert.ToInt32(DateTime.Today.ToString("yyyyMM"));
                            acrbatchinput_GL.responsible = user.agresso_user;//31122013 "DEV01";
                            acrbatchinput_GL.ext_ref = user.agresso_user;//31122013 "DEV01";
                            acrbatchinput_GL.batch_id = batchID;
                            acrbatchinput_GL.interface_ = "SC";
                            acrbatchinput_GL.disc_date = acrbatchinput_AP.trans_date;
                            acrbatchinput_GL.arrival_date = acrbatchinput_AP.trans_date;
                            acrbatchinput_GL.sequence_no = GetSequenceRunningNumber();
                            acrbatchinputList.Add(acrbatchinput_GL);
                            totalGL += acrbatchinput_GL.amount;
                        }
                    }


                    if (acrbatchinput_AP.amount + totalGL != 0)
                    {
                        foreach (Statement item in stt.Items)
                        {
                            updateRemarks(item.i_statement_id, "-[E002] AP and GL is not balance, please contact administrator", item.remarks, "2");
                        }
                        continue;
                    }

                    //test++;

                    string agrtid = "";
                    foreach (acrbatchinput acr in acrbatchinputList)
                    {
                        m_AcrBatcInputService.Insert(acr);
                        m_AcrBatcInputService.InsertToInterfaceTable(acr);
                        agrtid += ";" + acr.agrtid.ToString();
                    }
                    //update status-------
                    foreach (Statement item in stt.Items)
                    {
                        cmd.CommandText = StatementSQL.UpdateStatementPickup_flagStatus();
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@pickup_flag", SqlDbType.NVarChar).Value = "1";
                        cmd.Parameters.Add("@pickup_date", SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.Parameters.Add("@pickup_update_date", SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.Parameters.Add("@agrtidList", SqlDbType.NVarChar).Value = agrtid.Length > 50 ? agrtid.Substring(0, 50) : agrtid;
                        cmd.Parameters.Add("@i_statement_id", SqlDbType.Int).Value = item.i_statement_id;
                        cmd.Parameters.Add("@remarks", SqlDbType.NVarChar).Value = "";
                        cmd.Parameters.Add("@batch_id", SqlDbType.NVarChar).Value = batchID;
                        cmd.ExecuteNonQuery();
                    }
                }
                foreach (Statement stt in statementsCommissionCredit)
                {
                    //  stt.voyage_no = 999;
                    //  if (test == 5) break;
                    // if (stt.voyage_no == 0)
                    //   stt.voyage_no = GetVoyageNumberByStatementNumber(stt.statement_number);
                    SupplierCustomer supplier = m_suppCusService.GetSupplierMapping(stt.registered_owner == "" ? stt.disponent_owner : stt.registered_owner);
                    ProfitCentre profitCentre = m_profitCentreService.GetProfitCentreMapping(stt.profit_centre);
                    ResponsibleUser user = m_ResponsibleUserService.GetResponsibleUserByName(stt.fixed_by, "POSTFIX");
                    m_vesselService.FindVesselByName(stt.vessel_name,stt.vessel_sid, stt.profit_centre); // vessel validation inserting if not found------------
                    // ChargeTypeAccountMapping chargeType = m_chargeTypeAccountMappingService.GetChargeTypeAccountMapping(stt.charge_type);
                    string alert = "";
                    if (supplier.agr_supp_apar_id == "") { alert += string.Format("-[E005] Unable to find apar_ID for Company '{0}' \r\n", supplier.full_name); };
                    if (profitCentre.agr_dim_value == "") { alert += string.Format("-[E004] Unable to find Dim_Val for Profit Centre '{0}' \r\n", profitCentre.full_name); }//alert
                    if (profitCentre.agr_rel_value == "") { alert += string.Format("-[E008] Unable to find Cost Centre Code for Profit Centre '{0}' \r\n", profitCentre.full_name); }//alert
                    if (profitCentre.client == "") { alert += string.Format("-[E007] Unable to find client for Profit Centre '{0}' \r\n", profitCentre.full_name); }//alert
                    Vessel profitCentreVessel = m_vesselService.GetVesselMappingByProfitCentreIDAndVesselName(profitCentre.profit_centre_id, stt.vessel_name.Trim(), profitCentre.client);
                    // bool validVoyage = m_ud_voyageimp_Service.IsRegisteredVoyage(profitCentreVessel == null ? "" : profitCentreVessel.agr_dim_value, stt.voyage_no.ToString());
                    if (supplier.apar_gr_id.Trim().ToUpper() == "SBR")
                    {
                        agldimvalue icVessel = m_agldimvalueService.GetICVesselDimValue(profitCentreVessel == null ? "XXX" : profitCentreVessel.agr_dim_value);
                        if (profitCentreVessel != null && icVessel == null) { alert += string.Format("-[E012] Unable to find IC Vessel value Code for Vessel '{0}' \r\n", profitCentreVessel.agr_dim_value); } //alert could not  find vessel dim val
                    }
                    // agldimvalue icVoyage = m_agldimvalueService.GetICVoyageDimValue((profitCentreVessel == null ? "XXX" : (profitCentreVessel.agr_dim_value + "-V" + stt.voyage_no.ToString().PadLeft(3, '0'))));

                    if (profitCentreVessel == null) { alert += string.Format("-[E015] Unable to find Vessel '{0}' for Profit Centre '{1} {2}' \r\n", stt.vessel_name.Trim(), profitCentre.full_name, profitCentre.agr_dim_value); }
                    if (profitCentreVessel != null && profitCentreVessel.agr_dim_value == "") { alert += string.Format("-[E010] Unable to find Dim_Val Code for Vessel '{0}' \r\n", profitCentreVessel.vessel_name); }//alert
                    if (profitCentreVessel != null && profitCentreVessel.agr_rel_value == "") { alert += string.Format("-[E008] Unable to find Cost Centre Code for Vessel '{0}' \r\n", profitCentreVessel.vessel_name); }//alert
                    //  if (profitCentreVessel != null && icVoyage == null) { alert += string.Format("-Unable to find IC Voyage value Code for Voyage '{0}' \r\n", profitCentreVessel.agr_dim_value + "-V" + stt.voyage_no.ToString().PadLeft(3, '0')); } //alert could not  find vessel dim val

                    //if (!validVoyage)
                    //{
                    //    alert += string.Format("-[E016] Unable to Find Voyage in Voyage Master, Vessel: '{0}' Voyage: '{1}' Code:'{2}' \r\n", stt.vessel_name.Trim(), stt.voyage_no,
                    //        (profitCentreVessel != null ? profitCentreVessel.agr_dim_value : "") + "-V" + stt.voyage_no.ToString().PadLeft(3, '0'));
                    //}

                    else if (stt.headfixture_type == "")
                    {
                        alert += string.Format("-[E026] The statement contains no HeadFixture Type");
                    }

                    foreach (Statement item in stt.Items)
                    {
                        ChargeTypeAccountMapping itm = m_chargeTypeAccountMappingService.GetChargeTypeAccountMapping(item.charge_type);
                        if (stt.headfixture_type == "TC Charter")
                        {
                            if (itm.tcrelet_account == "") { alert += string.Format("-[E017] Unable to find Account Map for Charge Type '{0}' TC Charter", item.charge_type); } //alert coudnot map account on charge type :---------
                        }
                        else if (stt.headfixture_type == "BB")
                        {
                            if (itm.bareboatrelet_account == "") { alert += string.Format("-[E017] Unable to find Account Map for Charge Type '{0}'  Bareboat", item.charge_type); } //alert coudnot map account on charge type :---------
                        }
                    }

                    if (alert != "")
                    {
                        foreach (Statement item in stt.Items)
                        {
                            updateRemarks(item.i_statement_id, alert, item.remarks, "0");
                        }
                        //m_alertService.CheckAndUpdateAlert(stt.i_statement_id.ToString(), AlertType.SOFTMAR_STATEMENT, AlertStatus.PENDING,
                        //    string.Format("Failed while interfacing Invoice No.{0}, Invoice Date :{3}, Company Name : {1}, Description : {2}  \r\n", stt.statement_number, stt.registered_owner == "" ? stt.disponent_owner : stt.registered_owner
                        //    , stt.charge_description, stt.statement_date.ToString("dd-MMM-yyyy")) + alert, stt.statement_number);
                        continue;
                    }
                    //else
                    //{
                    //    m_alertService.CheckAndUpdateAlert(stt.i_statement_id.ToString(), AlertType.SOFTMAR_STATEMENT, AlertStatus.RESOLVED,
                    //          string.Format("Failed while interfacing Invoice No.{0}, Invoice Date :{3}, Company Name : {1}, Description : {2}  \r\n", stt.statement_number, stt.registered_owner == "" ? stt.disponent_owner : stt.registered_owner
                    //        , stt.charge_description, stt.statement_date.ToString("dd-MMM-yyyy")) + alert, stt.statement_number);
                    //}
                    IList acrbatchinputList = new ArrayList();
                    int voucherNumber = GetVoucherRunningNumber();
                    string batchID = "SC" + DateTime.Now.ToString("ddMMyyyyHHmmss");

                    acrbatchinput acrbatchinput_AP = new acrbatchinput();
                    acrbatchinput_AP.voucher_type = "SC";
                    acrbatchinput_AP.trans_type = "AP";
                    acrbatchinput_AP.client = profitCentre.client;
                    acrbatchinput_AP.apar_type = "P";
                    acrbatchinput_AP.apar_id = supplier.agr_supp_apar_id;
                    acrbatchinput_AP.account = "";
                    acrbatchinput_AP.dim_1 = profitCentre.agr_rel_value;
                    acrbatchinput_AP.dim_2 = "";// profitCentreVessel.agr_rel_value;
                    acrbatchinput_AP.dim_3 = "";
                    acrbatchinput_AP.dim_4 = profitCentreVessel.agr_dim_value + "-V" + stt.voyage_no.ToString().PadLeft(3, '0');
                    acrbatchinput_AP.dim_5 = profitCentreVessel.agr_dim_value;
                    acrbatchinput_AP.dim_6 = profitCentre.agr_dim_value;
                    acrbatchinput_AP.dim_7 = "";
                    acrbatchinput_AP.tax_code = "0";
                    acrbatchinput_AP.tax_system = "";
                    acrbatchinput_AP.currency = stt.statement_currency;
                    acrbatchinput_AP.cur_amount = -stt.statement_total;//-ci.invoice_amount;
                    acrbatchinput_AP.amount = -stt.statement_total;//-ci.invoice_amount;
                    acrbatchinput_AP.description = stt.charge_description + "-" + stt.statement_number + "-" + stt.raised_to_final_by;
                    acrbatchinput_AP.trans_date = stt.statement_date;
                    acrbatchinput_AP.voucher_date = stt.statement_date;
                    acrbatchinput_AP.voucher_no = voucherNumber;// 1;
                    acrbatchinput_AP.ext_inv_ref = stt.statement_number;
                    acrbatchinput_AP.status = "N";
                    acrbatchinput_AP.due_date = stt.statement_due_date;
                    acrbatchinput_AP.period = Convert.ToInt32(DateTime.Today.ToString("yyyyMM"));
                    acrbatchinput_AP.responsible = user.agresso_user;//31122013 "DEV01";
                    acrbatchinput_AP.batch_id = batchID;
                    acrbatchinput_AP.interface_ = "SC";
                    acrbatchinput_AP.sequence_no = GetSequenceRunningNumber();
                    acrbatchinput_AP.disc_date = acrbatchinput_AP.trans_date;
                    acrbatchinput_AP.arrival_date = acrbatchinput_AP.trans_date;
                    acrbatchinputList.Add(acrbatchinput_AP);

                    decimal totalGL = 0;
                    foreach (Statement item in stt.Items)
                    {
                        if (item.narrative_line == 80)
                        {
                            ChargeTypeAccountMapping cam = m_chargeTypeAccountMappingService.GetChargeTypeAccountMapping("Charge Collect Comm");
                            acrbatchinput acrbatchinput_GL_cc = new acrbatchinput();
                            acrbatchinput_GL_cc.voucher_type = "SC";
                            acrbatchinput_GL_cc.trans_type = "GL";
                            acrbatchinput_GL_cc.client = profitCentre.client;
                            acrbatchinput_GL_cc.apar_type = "";
                            acrbatchinput_GL_cc.apar_id = supplier.agr_supp_apar_id;
                            acrbatchinput_GL_cc.account = stt.headfixture_type.Trim() == "TC Charter" ? cam.tcrelet_account : cam.bareboatrelet_account;//stt.headfixture_type.Trim() == "TC Charter" ? chargeType.tcrelet_account : chargeType.vcrelet_account;
                            acrbatchinput_GL_cc.dim_1 = profitCentreVessel.agr_rel_value;//"";
                            acrbatchinput_GL_cc.dim_2 = "";// profitCentreVessel.agr_rel_value;
                            acrbatchinput_GL_cc.dim_3 = "";
                            acrbatchinput_GL_cc.dim_4 = profitCentreVessel.agr_dim_value + "-V" + stt.voyage_no.ToString().PadLeft(3, '0');
                            acrbatchinput_GL_cc.dim_5 = profitCentreVessel.agr_dim_value;
                            acrbatchinput_GL_cc.dim_6 = profitCentre.agr_dim_value;
                            acrbatchinput_GL_cc.dim_7 = "";
                            //acrbatchinput_GL_cc.tax_code = stt.headfixture_type.Trim() == "TC Charter" ? cam.tcrelet_tax_account_singapore : cam.vcrelet_tax_account_singapore;//"0";
                            acrbatchinput_GL_cc.tax_code = stt.headfixture_type.Trim() == "TC Charter" ?
                                supplier.country_code_sup == "SG" ? cam.tcrelet_tax_account_singapore : cam.tcrelet_tax_account_ovs
                                : supplier.country_code_sup == "SG" ? cam.bareboatrelet_tax_account_singapore : cam.bareboatrelet_tax_account_ovs;//"0";
                            acrbatchinput_GL_cc.tax_system = "";
                            acrbatchinput_GL_cc.currency = stt.statement_currency;
                            acrbatchinput_GL_cc.cur_amount = -item.charge_amount;// 04092013 -item.charge_co_comm_amount;//ci.invoice_amount;
                            acrbatchinput_GL_cc.amount = -item.charge_amount;// 04092013  -item.charge_co_comm_amount;//ci.invoice_amount;
                            acrbatchinput_GL_cc.description = "Charge Collect Commission";//stt.charge_description + "-" + stt.statement_number + "-" + stt.raised_to_final_by;
                            acrbatchinput_GL_cc.trans_date = stt.statement_date;
                            acrbatchinput_GL_cc.voucher_date = stt.statement_date;
                            acrbatchinput_GL_cc.voucher_no = voucherNumber;//1;
                            acrbatchinput_GL_cc.ext_inv_ref = stt.statement_number;
                            acrbatchinput_GL_cc.status = "N";
                            acrbatchinput_GL_cc.due_date = stt.statement_due_date;
                            acrbatchinput_GL_cc.period = Convert.ToInt32(DateTime.Today.ToString("yyyyMM"));
                            acrbatchinput_GL_cc.responsible = user.agresso_user;//31122013 "DEV01";
                            acrbatchinput_GL_cc.batch_id = batchID;
                            acrbatchinput_GL_cc.interface_ = "SC";
                            acrbatchinput_GL_cc.disc_date = acrbatchinput_AP.trans_date;
                            acrbatchinput_GL_cc.arrival_date = acrbatchinput_AP.trans_date;
                            acrbatchinput_GL_cc.sequence_no = GetSequenceRunningNumber();
                            acrbatchinputList.Add(acrbatchinput_GL_cc);
                            totalGL += acrbatchinput_GL_cc.amount;
                        }
                        if (item.narrative_line == 90)//29-11-2013
                        {
                            ChargeTypeAccountMapping cam = m_chargeTypeAccountMappingService.GetChargeTypeAccountMapping("Charge Collect Comm");
                            acrbatchinput acrbatchinput_GL_cc = new acrbatchinput();
                            acrbatchinput_GL_cc.voucher_type = "SC";
                            acrbatchinput_GL_cc.trans_type = "GL";
                            acrbatchinput_GL_cc.client = profitCentre.client;
                            acrbatchinput_GL_cc.apar_type = "";
                            acrbatchinput_GL_cc.apar_id = supplier.agr_supp_apar_id;
                            acrbatchinput_GL_cc.account = stt.headfixture_type.Trim() == "TC Charter" ? cam.tcrelet_account : cam.bareboatrelet_account;//stt.headfixture_type.Trim() == "TC Charter" ? chargeType.tcrelet_account : chargeType.vcrelet_account;
                            acrbatchinput_GL_cc.dim_1 = profitCentreVessel.agr_rel_value;//"";
                            acrbatchinput_GL_cc.dim_2 = "";// profitCentreVessel.agr_rel_value;
                            acrbatchinput_GL_cc.dim_3 = "";
                            acrbatchinput_GL_cc.dim_4 = profitCentreVessel.agr_dim_value + "-V" + stt.voyage_no.ToString().PadLeft(3, '0');
                            acrbatchinput_GL_cc.dim_5 = profitCentreVessel.agr_dim_value;
                            acrbatchinput_GL_cc.dim_6 = profitCentre.agr_dim_value;
                            acrbatchinput_GL_cc.dim_7 = "";
                            //acrbatchinput_GL_cc.tax_code = stt.headfixture_type.Trim() == "TC Charter" ? cam.tcrelet_tax_account_singapore : cam.vcrelet_tax_account_singapore;//"0";
                            acrbatchinput_GL_cc.tax_code = stt.headfixture_type.Trim() == "TC Charter" ?
                                supplier.country_code_sup == "SG" ? cam.tcrelet_tax_account_singapore : cam.tcrelet_tax_account_ovs
                                : supplier.country_code_sup == "SG" ? cam.bareboatrelet_tax_account_singapore : cam.bareboatrelet_tax_account_ovs;//"0";
                            acrbatchinput_GL_cc.tax_system = "";
                            acrbatchinput_GL_cc.currency = stt.statement_currency;
                            acrbatchinput_GL_cc.cur_amount = -item.charge_amount;// 04092013 -item.charge_co_comm_amount;//ci.invoice_amount;
                            acrbatchinput_GL_cc.amount = -item.charge_amount;// 04092013  -item.charge_co_comm_amount;//ci.invoice_amount;
                            acrbatchinput_GL_cc.description = "Charge Collect Commission";//stt.charge_description + "-" + stt.statement_number + "-" + stt.raised_to_final_by;
                            acrbatchinput_GL_cc.trans_date = stt.statement_date;
                            acrbatchinput_GL_cc.voucher_date = stt.statement_date;
                            acrbatchinput_GL_cc.voucher_no = voucherNumber;//1;
                            acrbatchinput_GL_cc.ext_inv_ref = stt.statement_number;
                            acrbatchinput_GL_cc.status = "N";
                            acrbatchinput_GL_cc.due_date = stt.statement_due_date;
                            acrbatchinput_GL_cc.period = Convert.ToInt32(DateTime.Today.ToString("yyyyMM"));
                            acrbatchinput_GL_cc.responsible = user.agresso_user;//31122013 "DEV01";
                            acrbatchinput_GL_cc.batch_id = batchID;
                            acrbatchinput_GL_cc.interface_ = "SC";
                            acrbatchinput_GL_cc.disc_date = acrbatchinput_AP.trans_date;
                            acrbatchinput_GL_cc.arrival_date = acrbatchinput_AP.trans_date;
                            acrbatchinput_GL_cc.sequence_no = GetSequenceRunningNumber();
                            acrbatchinputList.Add(acrbatchinput_GL_cc);
                            totalGL += acrbatchinput_GL_cc.amount;
                        }
                    }
                    if (acrbatchinput_AP.amount + totalGL != 0)
                    {
                        foreach (Statement item in stt.Items)
                        {
                            updateRemarks(item.i_statement_id, "-[E002] AP and GL is not balance, please contact administrator", item.remarks, "2");
                        }
                        continue;
                    }

                    //test++;

                    string agrtid = "";
                    foreach (acrbatchinput acr in acrbatchinputList)
                    {
                        m_AcrBatcInputService.Insert(acr);
                        m_AcrBatcInputService.InsertToInterfaceTable(acr);
                        agrtid += ";" + acr.agrtid.ToString();
                    }
                    //update status-------
                    foreach (Statement item in stt.Items)
                    {
                        cmd.CommandText = StatementSQL.UpdateStatementPickup_flagStatus();
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@pickup_flag", SqlDbType.NVarChar).Value = "1";
                        cmd.Parameters.Add("@pickup_date", SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.Parameters.Add("@pickup_update_date", SqlDbType.DateTime).Value = DateTime.Now;
                        cmd.Parameters.Add("@agrtidList", SqlDbType.NVarChar).Value = agrtid.Length > 50 ? agrtid.Substring(0, 50) : agrtid;
                        cmd.Parameters.Add("@i_statement_id", SqlDbType.Int).Value = item.i_statement_id;
                        cmd.Parameters.Add("@remarks", SqlDbType.NVarChar).Value = "";
                        cmd.Parameters.Add("@batch_id", SqlDbType.NVarChar).Value = batchID;
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        private void updateRemarks(int p, string alert, string oldAlert, string pickupFlag)
        {
            if (alert == oldAlert) return;
            m_dataBaseConectivity.InterfaceSqlConnection_b.Open();
            SqlCommand cmd = new SqlCommand(StatementSQL.UpdateStatementPickup_flagStatus(), m_dataBaseConectivity.InterfaceSqlConnection_b);
            cmd.Parameters.Add("@pickup_flag", SqlDbType.NVarChar).Value = pickupFlag;
            cmd.Parameters.Add("@pickup_date", SqlDbType.DateTime).Value = DateTime.Now;
            cmd.Parameters.Add("@pickup_update_date", SqlDbType.DateTime).Value = DateTime.Now;
            cmd.Parameters.Add("@agrtidList", SqlDbType.NVarChar).Value = "";
            cmd.Parameters.Add("@i_statement_id", SqlDbType.Int).Value = p;
            cmd.Parameters.Add("@remarks", SqlDbType.NVarChar).Value = alert;
            cmd.Parameters.Add("@batch_id", SqlDbType.NVarChar).Value = "";
            cmd.ExecuteNonQuery();
            m_dataBaseConectivity.InterfaceSqlConnection_b.Close();
        }
        private int GetSequenceRunningNumber()
        {
            ConfigurationSetting runningNumber = m_configSettingService.GetConfigurationSetting("RunningNumber");
            int number = Convert.ToInt32(runningNumber.val_2) + 1;
            runningNumber.val_2 = number.ToString();
            m_configSettingService.UpdateConfiguration(runningNumber);
            return number;
        }
        private int GetVoucherRunningNumber()
        {
            ConfigurationSetting runningNumber = m_configSettingService.GetConfigurationSetting("RunningNumber");
            int number = Convert.ToInt32(runningNumber.val_1) + 1;
            runningNumber.val_1 = number.ToString();
            m_configSettingService.UpdateConfiguration(runningNumber);
            return number;
        }
        public IList GetAllPendingStatement(string p)
        {
            IList commissionInvoices = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = StatementSQL.GetAllPendingStatement(p);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    commissionInvoices.Add(new Statement(rd));
                }
                rd.Close();
                GetAllPendingFreightHireOUTCommission(commissionInvoices, p);
                return commissionInvoices;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public IList GetAllPendingStatement()
        {
            IList commissionInvoices = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = StatementSQL.GetAllPendingStatement();
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    commissionInvoices.Add(new Statement(rd));
                }
                rd.Close();
                GetAllPendingFreightHireOUTCommission(commissionInvoices);
                return commissionInvoices;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public IList GetAllPendingFreightHireOUTCommission(IList commissionInvoices)
        {
            try
            {
                //m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = CommisionInvoiceSQL.GetAllPendingFreightHireOUTCommission();
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    commissionInvoices.Add(Statement.StatementFromFreightHireInvoice(rd));
                }
                rd.Close();
                return commissionInvoices;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                // m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public IList GetAllPendingFreightHireOUTCommission(IList commissionInvoices, string search)
        {
            try
            {
                //  m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = CommisionInvoiceSQL.GetAllPendingFreightHireOUTCommission(search);
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    commissionInvoices.Add(Statement.StatementFromFreightHireInvoice(rd));
                }
                rd.Close();
                return commissionInvoices;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                // m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public int GetAllPendingStatementCount()
        {
            int commissionInvoices = 0;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = StatementSQL.GetAllPendingStatementCount();
                commissionInvoices = Convert.ToInt32(cmd.ExecuteScalar());
                return commissionInvoices;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
        public void UpdateFixedBy()
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = "select distinct statement_number from i_statement  where pickup_flag in (0,1) and fixed_by is null";
                SqlDataReader rd = cmd.ExecuteReader();
                IList statmentnumbers = new ArrayList();
                while (rd.Read())
                {
                    statmentnumbers.Add(rd["statement_number"].ToString());
                }
                rd.Close();
                foreach (string no in statmentnumbers)
                {
                    cmd.Connection = m_dataBaseConectivity.SoftmarSqlConnection;
                    cmd.CommandText = @" select distinct 	
                        vVMHeadOpfix.Broker Fixed_By
                         from vStmtSrch 
	                         join VVMHEADOPFIX on 
	                        VVMHEADOPFIX.ID = VSTMTSRCH.FIX 
	                        join VOPINVOICELINENARRATIVE on
	                        VOPINVOICELINENARRATIVE.InvoiceNo = VSTMTSRCH.No
	                        and  VOPINVOICELINENARRATIVE.Fixture = VSTMTSRCH.Fix and
	                        VOPINVOICELINENARRATIVE.InvoiceNo = VSTMTSRCH.No and
	                        VOPINVOICELINENARRATIVE.Charge is not null  and
	                        VOPINVOICELINENARRATIVE.VoyageNo =  VSTMTSRCH.VyNo and
	                        VOPINVOICELINENARRATIVE.GroupNo = VSTMTSRCH.GrNo and
	                        VOPINVOICELINENARRATIVE.OptionNo = VSTMTSRCH.OpNo
	                        JOIN VOPORG9SRCH ON VVMHEADOPFIX.COMPANY=vOpOrg9Srch.ID 
	                        JOIN VOPHEADFIXCHARGESRCH ON VVMHEADOPFIX.ID=VOPHEADFIXCHARGESRCH.FIXTURE 
	                        join VOPCHARGEFIXTURESRCH on VOPCHARGEFIXTURESRCH.ID = VOPINVOICELINENARRATIVE.ChargeID
	                        LEFT OUTER JOIN VVMREMIT ON VVMHEADOPFIX.ID = VVMREMIT.FIXTURE AND 
                         VSTMTSRCH.GRNO = VVMREMIT.GROUPNO AND 
                         VSTMTSRCH.FIX = VVMREMIT.FIXTURE AND 
                         VSTMTSRCH.OPNO = VVMREMIT.OPTIONNO AND 
                         VSTMTSRCH.VYNO = VVMREMIT.VOYAGENO AND 
                         VSTMTSRCH.NO = VVMREMIT.NO 
	                         where 
	                         vVMHeadOpfix.vmStatus NOT IN ('UnFixed','Unfixed COA Lifting','Cargo Booking') 
	                          AND vStmtSrch.DocInvDate  > '2013-07-23 00:00' 
                         AND vStmtSrch.InvoiceNo <> 'proforma'
                         AND vStmtSrch.InvoiceNo not like 'PPL%'
                         AND  vOpOrg9Srch.KeyName <> 'PACNAV'
                         AND vStmtSrch.Header <> 'Commission Credit Statement'
                         and vStmtSrch.InvoiceNo = @statementnumber";
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("@statementnumber", SqlDbType.NVarChar).Value = no;
                    if (cmd.ExecuteScalar() != null)
                    {
                        string fixedby = cmd.ExecuteScalar().ToString();

                        cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                        cmd.CommandText = "update i_statement set fixed_by =@fixed_by  where statement_number=@statement_number";
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("@statement_number", SqlDbType.NVarChar).Value = no;
                        cmd.Parameters.Add("@fixed_by", SqlDbType.NVarChar).Value = fixedby;
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        //update new column vessel_code
        public void UpdateVesselsCode()
        {
            IList commissionInvoices = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                m_dataBaseConectivity.SoftmarSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.CommandText = "select * from i_statement where pickup_flag != 0 and  statement_number != 'PROFORMA'";
                SqlCommand cmdSoftmar = new SqlCommand();
                cmdSoftmar.Connection = m_dataBaseConectivity.SoftmarSqlConnection;
                SqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    commissionInvoices.Add(new Statement(rd,true));
                }
                rd.Close();
                foreach (Statement st in commissionInvoices)
                {
                    cmdSoftmar.CommandText = "select distinct vVMHeadOpfix.VesselID vessel_code from vStmtSrch join VVMHEADOPFIX on VVMHEADOPFIX.ID = VSTMTSRCH.FIX where vStmtSrch.InvoiceNo = '" + st.statement_number + "'";
                    string vessel_code = "";
                    SqlDataReader rdSf = cmdSoftmar.ExecuteReader();
                    while (rdSf.Read())
                    {
                        vessel_code = rdSf[0].ToString();
                    }
                    rdSf.Close();
                    cmd.CommandText = string.Format("update i_statement set vessel_code = '{0}'  where i_statement_id = {1}", vessel_code, st.i_statement_id);
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                m_dataBaseConectivity.SoftmarSqlConnection.Close();
            }
        }
    }
}
